# -*- coding: utf-8 -*-

from datetime import datetime, timedelta
import pandas as pd
from sqlalchemy import create_engine
import json


port = 5432
host = '192.168.10.5'
database = 'ctp'
user = 'postgres'
password = 'steward1!'
df = pd.DataFrame()
try:
    # 使用SQLAlchemy创建引擎
    engine = create_engine(
        f"postgresql+psycopg2://{user}:{password}"
        f"@{host}:{port}/{database}"
    )

except Exception as e:
    print("数据库错误", f"连接数据库失败: {str(e)}")
sd = datetime.now() - timedelta(10)
ed = datetime.now()

instrument_id = 'v2509'
table_name = 'tmpl'
select_tick = f'''select "InstrumentID", "DateTime" as "Datetime",   
    "UpperLimitPrice", "LowerLimitPrice","OpenPrice", 	"HighestPrice", "LowestPrice", 
     "Volume", "Turnover", "AveragePrice" , "OpenInterest", "LastPrice",
    "BidPrice1", "BidVolume1", "AskPrice1", "AskVolume1"
    from tick.{table_name} 
    where "InstrumentID"='{instrument_id}' and "DateTime" between '{sd}' and '{ed}' 
    order by "DateTime" desc limit 1000;
    '''
table_name = 'bar'
select_bar = f"""
                SELECT date_time as date, open, close, low, high, volume 
                FROM bar.{table_name} 
                WHERE instrument_id = '{instrument_id}'
                ORDER BY date_time ASC
                limit 100;
            """

print(select_tick)

ticks = pd.read_sql(select_tick,engine)
# ticks['Datetime'] = ticks.ActionDay + ticks.UpdateTime + str(ticks.UpdateMillisec)
print(ticks.columns)
print(ticks.head(10))

sudo rpm -ivh --force https://mirrors.aliyun.com/centos-stream/10-stream/BaseOS/x86_64/os/Packages/centos-stream-release-10.0-11.el10.noarch.rpm
sudo rpm -ivh --force https://mirrors.aliyun.com/centos-stream/10-stream/BaseOS/x86_64/os/Packages/centos-gpg-keys-10.0-11.el10.noarch.rpm
sudo rpm -ivh --force https://mirrors.aliyun.com/centos-stream/10-stream/BaseOS/x86_64/os/Packages/centos-stream-repos-10.0-11.el10.noarch.rpm
centos-stream-repos-10.0-11.el10.noarch.rpm




